package com.yc.bbs.dao;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yc.bbs.entity.TblTopic;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 * BBS论坛-帖子表-19 Mapper 接口
 * </p>
 *
 * @author k
 * @since 2024-11-25
 */
public interface TblTopicMapper extends BaseMapper<TblTopic> {
    @Select("select a.*,b.cname \"user.cname\",reply_cnt\n" +
            "from tbl_topic a join tbl_user b on b.id = a.uid\n" +
            "left join (select count(*) reply_cnt,topicid from tbl_reply group by topicid) c\n" +
            "on a.id=c.topicid where boardid=#{bid} ")
    Page<TblTopic> selectByBid(@Param("bid") int bid, Page<TblTopic> page);

    @Select("SELECT\n" +
            "	a.*,\n" +
            "	b.cname \"user.cname\",\n" +
            "	b.head \"user.head\"," +
            "	b.regtime \"user.regtime\"," +
            "	reply_cnt \n" +
            "FROM\n" +
            "	tbl_topic a\n" +
            "	JOIN tbl_user b ON a.uid = b.id\n" +
            "	LEFT JOIN ( SELECT count(*) reply_cnt, topicid FROM tbl_reply GROUP BY topicid ) c ON a.id = c.topicid\n" +
            "where a.id = #{bid}")
    TblTopic queryById(int id);
}
